package dbutil

import (
	"bytes"
	"database/sql"
	"fmt"
	"github.com/aceld/zinx/utils"
	log "github.com/cihub/seelog"
	_ "github.com/go-sql-driver/mysql"
	"strconv"
	"time"
)


var DataProcessDb *sql.DB


type KeyValue struct {
	Key string 	`json:"key"`
	Unit string  `json:"unit"`
	Value string `json:"value"`
}


type Event struct {
	SitId 	  uint32 `json:"siteSid"`
	Level     int32  `protobuf:"varint,1,opt,name=level,proto3" json:"level,omitempty"`
	Type      int32  `protobuf:"varint,2,opt,name=type,proto3" json:"type,omitempty"`
	Sender    string `protobuf:"bytes,3,opt,name=sender,proto3" json:"sender,omitempty"`
	Content   string `protobuf:"bytes,4,opt,name=content,proto3" json:"content,omitempty"`
	CreatedAt string `protobuf:"bytes,5,opt,name=createdAt,proto3" json:"createdAt,omitempty"`
	KeyValue  string `protobuf:"bytes,6,opt,name=keyValue,proto3" json:"keyValue,omitempty"`
}

func DestroyMysqlPool()  {
	DataProcessDb.Close()
}

func InitMysqlPool() error{

	var err error
	// 打开数据库连接
	DataProcessDb, err = sql.Open("mysql", utils.GlobalObject.DSN4IIMSDatabase)
	if err != nil {
		log.Info(err)
		return err
	}

	DataProcessDb.SetMaxOpenConns(10)
	DataProcessDb.SetMaxIdleConns(5)
	DataProcessDb.SetConnMaxLifetime(15*time.Second)

	return nil
}

func SetDeviceStatus(siteid uint32,uuid string,status uint8)  {

	updateSql :=fmt.Sprintf("UPDATE `iims_database`.`iims_devices` SET `status` = %v WHERE `uuid` = '%v' AND `site_id` = %v AND `ldf` = 0",status,uuid,siteid)
	// 准备生成查询语句
	insertStmt, err := DataProcessDb.Prepare(updateSql)
	if err != nil {
		log.Info(err)
		return
	}
	defer insertStmt.Close()

	// 执行插入语句
	_, err = insertStmt.Exec()
	if err != nil {
		log.Info(err)
	}
}

func StorageDayMonthPointToMysql(devuuid string,pointType string,pValue float64,siteSid string,demandTime string,dataTime string){


	var insertSQLBuffer bytes.Buffer
	insertSQLBuffer.WriteString(" REPLACE INTO `iims_data_month_day` (`equip_sid`, `pvalue`, `ptype`,`site_sid`, `data_time`, `create_time`) VALUES ")

	insertSQLBuffer.WriteString(fmt.Sprintf("('%v', %.2f ,'%v' ,'%v' , '%v', '%v')", devuuid, pValue, pointType,siteSid,demandTime ,dataTime))

	insertSQLBuffer.WriteString(";")

	// 准备生成SQL语句
	insertSQL := insertSQLBuffer.String()
	log.Infof("======SQL：%v", insertSQL)
	// 准备生成查询语句
	insertStmt, err := DataProcessDb.Prepare(insertSQL)
	if err != nil {
		log.Info("storageCommond13Data:",err)
		return
	}
	defer insertStmt.Close()

	// 执行插入语句
	_, err = insertStmt.Exec()
	if err != nil {
		log.Info("storageCommond13Data:",err)
		return
	}else{

	}
}


func StorageDatacacheToMysql(devuuid string,pointType string,pValue float64,siteSid string,dataTime string){

	var insertSQLBuffer bytes.Buffer
	insertSQLBuffer.WriteString("REPLACE INTO `iims_data_cache` (`sensor_uuid`, `value`, `key`,`site_sid`, `created_at`) VALUES ")
	insertSQLBuffer.WriteString(fmt.Sprintf("('%v', %.2f ,'%v' ,'%v' , '%v')", devuuid, pValue, pointType,siteSid ,dataTime))

	insertSQLBuffer.WriteString(";")

	// 准备生成SQL语句
	insertSQL := insertSQLBuffer.String()
	log.Infof("======SQL：%v", insertSQL)
	// 准备生成查询语句
	insertStmt, err := DataProcessDb.Prepare(insertSQL)
	if err != nil {
		log.Info("StorageDatacacheToMysql:",err)
		return
	}
	defer insertStmt.Close()

	// 执行插入语句
	_, err = insertStmt.Exec()
	if err != nil {
		log.Info("StorageDatacacheToMysql:",err)
		return
	}else{

	}

}


func StorageNowPointToMysql(devuuid string,pointType string,pValue float64,siteSid string,demandTime string,dataTime string){


	var insertSQLBuffer bytes.Buffer
	insertSQLBuffer.WriteString(" REPLACE INTO `iims_data_now` (`equip_sid`, `pvalue`, `ptype`,`site_sid`, `data_time`, `create_time`,`update_time`) VALUES ")

		insertSQLBuffer.WriteString(fmt.Sprintf("('%v', %.2f ,'%v' ,'%v' , '%v', '%v', '%v')", devuuid, pValue, pointType,siteSid,demandTime ,dataTime,dataTime))

	    insertSQLBuffer.WriteString(";")

	// 准备生成SQL语句
	insertSQL := insertSQLBuffer.String()
	log.Infof("======SQL：%v", insertSQL)
	// 准备生成查询语句
	insertStmt, err := DataProcessDb.Prepare(insertSQL)
	if err != nil {
		log.Info("storageCommond13Data:",err)
		return
	}
	defer insertStmt.Close()

	// 执行插入语句
	_, err = insertStmt.Exec()
	if err != nil {
		log.Info("storageCommond13Data:",err)
		return
	}else{

	}
}


func StorageNoDemandPointToMysql(devuuid string,pointType string,pValue float64,siteSid string,dataTime string){


		var insertSQLBuffer bytes.Buffer

		insertSQLBuffer.Reset()
		if pointType=="10301"||pointType=="10302"||pointType=="10303"{
			insertSQLBuffer.WriteString("REPLACE INTO `iims_data_current` (`equip_sid`, `ptype`, `pvalue`,`site_sid`,`create_time`) VALUES ")

			pType, _ := strconv.Atoi(pointType)
			insertSQLBuffer.WriteString(fmt.Sprintf("('%v', '%v', %.2f, '%v', '%v')", devuuid,pType, pValue,siteSid, dataTime))

		}else if pointType=="10201"||
			pointType=="10202"||
			pointType=="10203"||
			pointType=="10204"||
			pointType=="10205"||
			pointType=="10206"{

			insertSQLBuffer.WriteString("REPLACE INTO `iims_data_voltage` (`equip_sid`, `ptype`, `pvalue`,`site_sid`, `create_time`) VALUES ")

			pType, _ := strconv.Atoi(pointType)
			insertSQLBuffer.WriteString(fmt.Sprintf("('%v', '%v', %.2f,'%v', '%v');", devuuid,pType, pValue ,siteSid, dataTime))


		}else if pointType=="10501"||
			pointType=="10502"||
			pointType=="10503"||
			pointType=="10504"||
			pointType=="10601"||
			pointType=="10602"||
			pointType=="10603"||
			pointType=="10604"||
			pointType=="10701"||
			pointType=="10702"||
			pointType=="10703"||
			pointType=="10704"{

			insertSQLBuffer.WriteString("REPLACE INTO `iims_data_power` (`equip_sid`, `ptype`, `pvalue`,`site_sid`, `create_time`) VALUES ")
			pType, _ := strconv.Atoi(pointType)
			insertSQLBuffer.WriteString(fmt.Sprintf("('%v', '%v', %.2f,'%v', '%v');", devuuid,pType, pValue ,siteSid, dataTime))

		}else if pointType=="10801"||
			pointType=="10802"||
			pointType=="10803"||
			pointType=="10804"||
			pointType=="109"{

			insertSQLBuffer.WriteString("REPLACE INTO `iims_data_power_factor` (`equip_sid`, `ptype`, `pvalue`,`site_sid`, `create_time`) VALUES ")
			pType, _ := strconv.Atoi(pointType)
			insertSQLBuffer.WriteString(fmt.Sprintf("('%v', '%v', %.2f,'%v', '%v');", devuuid,pType, pValue ,siteSid, dataTime))

		}else if pointType=="20101"||
			pointType=="20102"||
			pointType=="20103"||
			pointType=="20104"||
			pointType=="20105"||
			pointType=="20106"{

			insertSQLBuffer.WriteString("REPLACE INTO `iims_data_harmonic` (`equip_sid`, `ptype`, `pvalue`,`site_sid`, `create_time`) VALUES ")
			pType, _ := strconv.Atoi(pointType)
			insertSQLBuffer.WriteString(fmt.Sprintf("('%v', '%v', %.2f,'%v', '%v');", devuuid,pType, pValue ,siteSid, dataTime))

		}else if pointType=="202"||
			pointType=="203"{

			insertSQLBuffer.WriteString("REPLACE INTO `iims_data_unbalance` (`equip_sid`, `ptype`, `pvalue`,`site_sid`, `create_time`) VALUES ")
			pType, _ := strconv.Atoi(pointType)
			insertSQLBuffer.WriteString(fmt.Sprintf("('%v', '%v', %.2f,'%v', '%v');", devuuid,pType, pValue ,siteSid, dataTime))

		}

		// 准备生成SQL语句
		insertSQL := insertSQLBuffer.String()
		log.Infof("======SQL：%v", insertSQL)
		// 准备生成查询语句
		insertStmt, err := DataProcessDb.Prepare(insertSQL)
		if err != nil {
			log.Info(err)
			return
		}
		defer insertStmt.Close()

		// 执行插入语句
		_, err = insertStmt.Exec()
		if err != nil {
			log.Info(err)
		}

}

func UpdateSiteTime(SiteSID string) error{

	updateStmt, err := DataProcessDb.Prepare("UPDATE `iims_sites` SET `status` = ?,`data_time` = ? WHERE `site_sid` = ? AND `ldf` = 0;")

	log.Info("UPDATE site time")

	if err != nil {
		log.Info("UPDATE site time fail:",err)
		return err
	}


	log.Info("")

	dateTime := time.Now().Format("2006-01-02 15:04:05")
	// 执行更新语句
	log.Info(SiteSID)
	_, err = updateStmt.Exec(1,dateTime, SiteSID)
	log.Info(updateStmt)
	log.Info(dateTime+SiteSID)
	if err != nil {
		return err
	}

	return nil
}


func StoreEvent(event *Event) (bool,error) {

	// 拼接长SQL语句
	var insertSQLBuffer bytes.Buffer
	insertSQLBuffer.WriteString("REPLACE INTO `iims_events` (`level`, `type`, `sender`, `content`, `site_id`, `created_at`, `value`) VALUES ")
	//siteID := GetSiteIdByDevicePID(event.Sender)
	//if siteID == 0{
	//
	//	zlog.Errorf(fmt.Sprintf("%s设备获取站点号为0",event.Sender))
	//	return false, err
	//}

	//tcP事件 站点号为0
	insertSQLBuffer.WriteString(fmt.Sprintf("(%v, %v, '%v', '%v', %v, '%v', '%v')", event.Level, event.Type, event.Sender, event.Content, event.SitId, event.CreatedAt, event.KeyValue))

	// 准备生成SQL语句
	insertSQL := insertSQLBuffer.String()

	log.Debug(insertSQL)
	updateStmt, err := DataProcessDb.Prepare(insertSQL)
	if err != nil {
		log.Error(err.Error())
		return false,err
	}
	defer updateStmt.Close()

	// 执行语句
	_, err = updateStmt.Exec()
	if err != nil {
		log.Error(err.Error())
		return false,err
	}
	return true,nil

}

